﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Web.Configuration;

namespace LG.DB
{
    public class SqlHelper : DBHelper
    {
        public SqlHelper(string content, ConnectionType type)
            : base(content, type)
        { }

        public SqlHelper()
            : this(WebConfigurationManager.ConnectionStrings[0].ConnectionString, ConnectionType.ConnectionString)
        {}

        public SqlHelper(string connName)
            : this(connName, ConnectionType.ConnectionName)
        { }

        protected override void fillCmdParams_(DbCommand cmd)
        {
            if (_params != null)
            {
                foreach (KeyValuePair<string, object> kv in _params)
                {
                    DbParameter p = new SqlParameter(kv.Key, kv.Value);
                    cmd.Parameters.Add(p);
                }
            }
            if (_outRetParams != null)
            {
                foreach (SqlParameter outp in _outRetParams)
                {
                    cmd.Parameters.Add(outp);
                }
            }
        }

        protected override DbCommand createCmd_(string cmdText, DbConnection conn)
        {
            return new SqlCommand(cmdText, (SqlConnection)conn);
        }

        protected override DbCommand createCmd_()
        {
            return new SqlCommand();
        }

        protected override DbDataAdapter createDa_(string cmdText, DbConnection conn)
        {
            return new SqlDataAdapter(cmdText, (SqlConnection)conn);
        }

        protected override DbConnection createConn_()
        {
            return new SqlConnection(_connectionString);
        }

        /// <summary>
        /// 2000里多表分页,单列排序
        /// </summary>
        /// <param name="tblName">要显示的表或多个表的连接</param>
        /// <param name="strGetFields">要显示的字段列表</param>
        /// <param name="fldName">排序字段，一般是主键</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="isDesc">是否倒序</param>
        /// <param name="strWhere">条件，不加where</param>
        /// <param name="count">符合条件的总记录数</param>
        /// <returns></returns>
        public override DataTable GetPageData(string tblName, string strGetFields, string fldName, int pageSize, int pageIndex, bool isDesc, string strWhere, out int count)
        {
            count = ExecScalar<int>(string.Format("select count(0) from {0} where {1}", tblName, strWhere));
            this.Params.Clear();
            this.Params.Add("tblName", tblName);
            this.Params.Add("strGetFields", strGetFields);
            this.Params.Add("fldName", fldName);
            this.Params.Add("pageSize", pageSize);
            this.Params.Add("pageIndex", pageIndex);
            this.Params.Add("doCount", false);
            this.Params.Add("orderType", isDesc);
            this.Params.Add("strWhere", strWhere);
            return RunDataTable("sp_pager");
        }

        /// <summary>
        /// 2005+里多表分页,单列排序
        /// </summary>
        /// <param name="tableName">要显示的表或多个表的连接</param>
        /// <param name="fields">要显示的字段列表</param>
        /// <param name="sortfield">排序字段</param>
        /// <param name="singleSortType">排序方法，false为升序，true为降序</param>
        /// <param name="pageSize">每页显示的记录个数</param>
        /// <param name="pageIndex">要显示那一页的记录</param>
        /// <param name="condition">查询条件,不需where</param>
        /// <param name="count">查询到的记录数</param>
        /// <returns></returns>
        public DataTable TablesPage(string tableName, string fields, string sortfield, bool singleSortType, int pageSize, int pageIndex, string condition, out int count)
        {
            this.Params.Clear();
            this.Params.Add("tblName", tableName);
            this.Params.Add("fields", fields);
            this.Params.Add("sortfields", sortfield);
            this.Params.Add("singleSortType", singleSortType ? "1" : "0");
            this.Params.Add("pageSize", pageSize);
            this.Params.Add("pageIndex", pageIndex);
            this.Params.Add("strCondition", condition);
            SqlParameter p = new SqlParameter("@Counts", SqlDbType.Int);
            this.OutRetParams.Add(p);
            DataTable dt = this.RunDataTable("sp_TablesPage");
            count = (int)p.Value;
            return dt;
        }

        /// <summary>
        /// 2005+里多表分页,多列排序
        /// </summary>
        /// <param name="tableName">要显示的表或多个表的连接</param>
        /// <param name="fields">要显示的字段列表</param>
        /// <param name="sortfields">一定要带asc或者desc</param>
        /// <param name="pageSize">每页显示的记录个数</param>
        /// <param name="pageIndex">要显示那一页的记录</param>
        /// <param name="condition">查询条件,不需where</param>
        /// <param name="count">查询到的记录数</param>
        /// <returns></returns>
        public DataTable TablesPage(string tableName, string fields, string sortfields, int pageSize, int pageIndex, string condition, out int count)
        {
            return TablesPage(tableName, fields, sortfields, true, pageSize, pageIndex, condition, out count);
        }
    }
}
